package tw.com.softleader.JGB.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import tw.com.softleader.JGB.model.GuestBookModel;

public class GuestBookDao {


	private static final String DB_URL = "jdbc:sqlserver://msdb.softleader.com.tw;databaseName=20140716test";
	private static final String USER = "sa";
	private static final String PASSWD = "sldb01";

	private static final String INSERT = "insert into guestbook (name, title, content) values (?, ?, ?)";

	public void insert(GuestBookModel model) {

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			try {
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}
			conn = DriverManager.getConnection(DB_URL, USER, PASSWD);
			stmt = conn.prepareStatement(INSERT);

			stmt.setString(1, model.getName());
			stmt.setString(2, model.getTitle());
			stmt.setString(3, model.getContent());

			stmt.execute();

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}

	}

	private static final String FIND_ALL = "select * from guestbook";

	public List<GuestBookModel> select() {

		List<GuestBookModel> page = null;

		Connection conn = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			try {
				Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
			} catch (ClassNotFoundException e) {
				e.printStackTrace();
			}				
			conn = DriverManager.getConnection(DB_URL, USER, PASSWD);
			stmt = conn.prepareStatement(FIND_ALL);
			rs = stmt.executeQuery();

			page = new ArrayList<GuestBookModel>();

			while (rs.next()) {
				GuestBookModel temp = new GuestBookModel();

				temp.setContent(rs.getString("content"));
				temp.setName(rs.getString("name"));
				temp.setTitle(rs.getString("title"));
				page.add(temp);
			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {

			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (stmt != null) {
				try {
					stmt.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}

		}

		return page;
	}

}
